package com.gis.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gis.dto.SedimentReportDTO;
import com.gis.entity.SedimentData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface SedimentDataMapper extends BaseMapper<SedimentData> {
    @Select("SELECT " +
            "sm.station_id, " +
            "s.name, " +
            "s.latitude, " +
            "s.longitude, " +
            "COALESCE(AVG(sm.mercury), -1.0) AS mercury, " +
            "COALESCE(AVG(sm.lead), -1.0) AS lead, " +
            "COALESCE(AVG(sm.cadmium), -1.0) AS cadmium, " +
            "COALESCE(AVG(sm.zinc), -1.0) AS zinc, " +
            "COALESCE(AVG(sm.arsenic), -1.0) AS arsenic, " +
            "COALESCE(AVG(sm.copper), -1.0) AS copper, " +
            "COALESCE(AVG(sm.chromium), -1.0) AS chromium, " +
            "COALESCE(AVG(sm.petroleum_class), -1.0) AS petroleum_class, " +
            "COALESCE(AVG(sm.oc), -1.0) AS oc, " +
            "COALESCE(AVG(sm.sulfide), -1.0) AS sulfide " +
            "FROM sediment_data sm " +
            "JOIN station_data s ON sm.station_id = s.id " +
            "WHERE sm.year = #{year} AND sm.season = #{season} " +
            "GROUP BY sm.station_id, s.name, s.latitude, s.longitude " +
            "ORDER BY s.name")
    List<SedimentReportDTO> getSedimentQualityAvgByYearAndSeason(int year, String season);

    @Select("SELECT * " +
            "FROM sediment_data " +
            "WHERE station_id = #{id} AND #{param} IS NOT NULL " +
            "ORDER BY year DESC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 1 " +
            "             WHEN season = 'summer' THEN 2 " +
            "             WHEN season = 'fall' THEN 3 " +
            "             WHEN season = 'winter' THEN 4 " +
            "             ELSE 5 " +
            "         END DESC " +
            "LIMIT #{n}")
    List<SedimentData> getLastN(String param, int n, int id);


    @Select("SELECT * " +
            "FROM sediment_data " +
            "WHERE station_id = #{id} " +
            "AND (year >= #{fromYear} AND year <= #{toYear}) " +
            "ORDER BY year ASC, " +
            "         CASE " +
            "             WHEN season = 'spring' THEN 4 " +
            "             WHEN season = 'summer' THEN 3 " +
            "             WHEN season = 'fall' THEN 2 " +
            "             WHEN season = 'winter' THEN 1 " +
            "             ELSE 5 " +
            "         END DESC")
    List<SedimentData> getFromYear(@Param("id") int stationId, int fromYear, int toYear);
}
